<?

//Initiate Authenticate
SecurityRedirect ();

//initiate Vars
define("PRIMARY_KEY","nobkt");
$PRIMARY_KEY    = PRIMARY_KEY;
$PRIMARY_VALUE  = GetParam($PRIMARY_KEY,"");
$FileName       = "ais_transaction_cashbankreceived.php";
$FileTemplate   = "ais_transaction_cashbankreceived_form.html";
$SubHeaderTitle = "Transaction Cash/Bank - Received";
$TableName      = "ais_memorial";
$HTMLFormName   = "CashBankForm";
$MaxGrid        = "100";
$KTRASL         = GetOfficeID();
//$HTMLFormName2  = "BudgetForm";

$FormAction    = $FileName . "?" . AddParam(GetQueryString("QueryString", ""),"form","$HTMLFormName");
$FormEncType   = "application/x-www-form-urlencoded";
//$PMode         = "";

//Initiate Grid Fields;
$FormFields    = array("uraian"=>"0",
            "urut"=>"",
            "skada"=>"",
            "jumlah"=>"number",
            "dk"=>"","noorder"=>"",
            "noref"=>"",
            "kdact"=>"",
			"jo_code"=>"",
            "memorial_id"=>"");

//buildsqlfield ("$TableName", $FormFields);

//Leave Here Untouch
$FormCName     = BuildHTMLField($FormFields,"name",1);
$FormCValue    = BuildHTMLField($FormFields,"value",1);

//************ FORM ACTION **************
if (trim(GetParam("form",""))==trim($HTMLFormName)) {
    if (GetParam("FormAction","")=="Save") {
        list($FDay,$FMonth,$FYear) = explode("/",GetParam("tglbkt",""));
        $tglbkt = "$FYear-$FMonth-$FDay";
        $rate    = StrToNumber (GetParam("rate",""));

        if (trim(GetParam("nobkt",""))=="AUTO NUMBERED BY SYSTEM")  {
            if (GetParam("type","")=="Bank") {
                $ObjNumber  = new autonumber(GetParam("tglbkt",""),GetParam("kdbank",""));
                $TransNo = $ObjNumber->GetNumber("TB");
            } else {
                $ObjNumber  = new autonumber(GetParam("tglbkt",""),GetParam("kdcash",""));
                $TransNo = $ObjNumber->GetNumber("TK");
            }
            FormSave("ais_memorial_z", "jumlah", $FormFields, array("ledger"=>GetPeriod(GetParam("tglbkt","")),"curr"=>GetParam("curr",""),"debtur"=>GetParam("debtur",""),"debnam"=>GetParam("debnam",""),"rate"=>$rate,"tglbkt"=>$tglbkt,"nobkt"=>$TransNo,"ktrasl"=>GetOfficeID(),"status"=>GetParam("status",""),"source"=>GetParam("source",""),"kdbank"=>GetParam("kdbank",""),"kdcash"=>GetParam("kdcash",""),"app1"=>GetParam("app1",""),"nmapp1"=>GetParam("nmapp1",""),"app2"=>GetParam("app2",""),"nmapp2"=>GetParam("nmapp2",""),"duedate"=>GetParam("duedate",""),"nocek"=>GetParam("nocek",""),"payby"=>GetParam("payby","")),"memorial_id");

            $SQL = "SELECT SUM(ais_memorial_z.rupiah) FROM ais_memorial_z WHERE nobkt='$TransNo'";
            $Rupiah = $DBConnection->dbc->get_var($SQL);

            $DBConnection->add_data("ais_memorial_z","source",GetParam("source",""));
            $DBConnection->add_data("ais_memorial_z","urut","999");
            $DBConnection->add_data("ais_memorial_z","ktrasl",GetOfficeID());

            $DBConnection->add_data("ais_memorial_z","kdbank",GetParam("kdbank",""));
            $DBConnection->add_data("ais_memorial_z","kdcash",GetParam("kdcash",""));

            $DBConnection->add_data("ais_memorial_z","ledger",GetPeriod(GetParam("tglbkt","")));
            $DBConnection->add_data("ais_memorial_z","curr",GetParam("curr",""));
            $DBConnection->add_data("ais_memorial_z","duedate",GetParam("duedate",""));
            $DBConnection->add_data("ais_memorial_z","payby",GetParam("payby",""));
            $DBConnection->add_data("ais_memorial_z","nocek",GetParam("nocek",""));
            $DBConnection->add_data("ais_memorial_z","kdact",GetParam("kdact999",""));
            $DBConnection->add_data("ais_memorial_z","app1",GetParam("app1",""));
            $DBConnection->add_data("ais_memorial_z","app2",GetParam("app2",""));
            $DBConnection->add_data("ais_memorial_z","nmapp1",GetParam("nmapp1",""));
            $DBConnection->add_data("ais_memorial_z","nmapp2",GetParam("nmapp2",""));

            $DBConnection->add_data("ais_memorial_z","rate",$rate);
            $DBConnection->add_data("ais_memorial_z","nobkt",$TransNo);
            $DBConnection->add_data("ais_memorial_z","tglbkt",$tglbkt);
            $DBConnection->add_data("ais_memorial_z","dk",GetParam("dk999",""));
            $DBConnection->add_data("ais_memorial_z","noorder",GetParam("noorder999",""));
            $DBConnection->add_data("ais_memorial_z","debtur",GetParam("debtur",""));
            $DBConnection->add_data("ais_memorial_z","debnam",GetParam("debnam",""));
            $DBConnection->add_data("ais_memorial_z","skada",GetParam("skada999",""));
            $DBConnection->add_data("ais_memorial_z","uraian",GetParam("uraian999",""));
            $DBConnection->add_data("ais_memorial_z","jumlah",StrToNumber(GetParam("total_trans","")));

            //$DBConnection->add_data("ais_memorial_z","rupiah",StrToNumber(GetParam("total_trans","")) * $rate);

            $DBConnection->add_data("ais_memorial_z","noref",GetParam("noref1",""));
            $DBConnection->add_data("ais_memorial_z","status",GetParam("status",""));
            $DBConnection->InsertGrid();

            SetLogActivity($TransNo,"Create","");
            //$DBConnection->dbc->query("UPDATE ais_memorial_history SET memo_jour='".$TransNo."', jml_memo='".StrToNumber(GetParam("total_trans",""))."' WHERE memoesti='".GetParam("aslref","")."' AND ktrasl='".GetOfficeID()."'");
        } else {
            $TransNo = GetParam("nobkt","");
            $SQL = "SELECT kdcash FROM ais_memorial WHERE nobkt='".$TransNo."' AND urut=999 ";
            $kdcash = $DBConnection->dbc->get_var($SQL);
            $SQL = "SELECT kdbank FROM ais_memorial WHERE nobkt='".$TransNo."' AND urut=999 ";
            $kdbank = $DBConnection->dbc->get_var($SQL);
            $SQL = "SELECT  jumlah FROM ais_memorial WHERE nobkt='".$TransNo."' AND urut=999 ";
            $jumlah = $DBConnection->dbc->get_var($SQL);

            if (ereg("TB",$TransNo)) {
                $SQL = "UPDATE tblbank SET balance=balance-".$jumlah." WHERE kdbank='".$kdbank."'";
            } else {
                $SQL = "UPDATE tblcash SET balance=balance-".$jumlah." WHERE kdcash='".$kdcash."'";
            }
            $DBConnection->dbc->query($SQL);

            $DBConnection->dbc->query("DELETE FROM ais_memorial WHERE nobkt='".$TransNo."' AND ktrasl='".GetOfficeID()."'");
            $DBConnection->dbc->query("DELETE FROM ais_piutang WHERE nobkt='".$TransNo."' AND ktrasl='".GetOfficeID()."'");
            $DBConnection->dbc->query("DELETE FROM ais_hutang WHERE nobkt='".$TransNo."' AND ktrasl='".GetOfficeID()."'");

            FormSave("ais_memorial", "jumlah", $FormFields, array("ledger"=>GetPeriod(GetParam("tglbkt","")),"curr"=>GetParam("curr",""),"debtur"=>GetParam("debtur",""),"debnam"=>GetParam("debnam",""),"rate"=>$rate,"tglbkt"=>$tglbkt,"nobkt"=>$TransNo,"ktrasl"=>GetOfficeID(),"status"=>GetParam("status",""),"source"=>GetParam("source",""),"kdbank"=>GetParam("kdbank",""),"kdcash"=>GetParam("kdcash",""),"app1"=>GetParam("app1",""),"nmapp1"=>GetParam("nmapp1",""),"app2"=>GetParam("app2",""),"nmapp2"=>GetParam("nmapp2",""),"duedate"=>GetParam("duedate",""),"nocek"=>GetParam("nocek",""),"payby"=>GetParam("payby","")),"memorial_id");

            $SQL = "SELECT SUM(ais_memorial.rupiah) FROM ais_memorial WHERE nobkt='$TransNo' and urut <> 999";
            $Rupiah = $DBConnection->dbc->get_var($SQL);


            if (ereg("TB",$TransNo)) {
                $SQL = "UPDATE tblbank SET balance=balance+".StrToNumber(GetParam("total_trans",""))." WHERE kdbank='".GetParam("kdbank","")."'";
            } else {
                $SQL = "UPDATE tblcash SET balance=balance+".StrToNumber(GetParam("total_trans",""))." WHERE kdcash='".GetParam("kdcash","")."'";
            }
            $DBConnection->dbc->query($SQL);

            $DBConnection->add_data("ais_memorial","source",GetParam("source",""));
            $DBConnection->add_data("ais_memorial","urut","999");
            $DBConnection->add_data("ais_memorial","ktrasl",GetOfficeID());
            $DBConnection->add_data("ais_memorial","kdbank",GetParam("kdbank",""));
            $DBConnection->add_data("ais_memorial","kdcash",GetParam("kdcash",""));
            $DBConnection->add_data("ais_memorial","ledger",GetPeriod(GetParam("tglbkt","")));
            $DBConnection->add_data("ais_memorial","curr",GetParam("curr",""));
            $DBConnection->add_data("ais_memorial","duedate",GetParam("duedate",""));
            $DBConnection->add_data("ais_memorial","payby",GetParam("payby",""));
            $DBConnection->add_data("ais_memorial","nocek",GetParam("nocek",""));

            $DBConnection->add_data("ais_memorial","kdact",GetParam("kdact999",""));
            $DBConnection->add_data("ais_memorial","app1",GetParam("app1",""));
            $DBConnection->add_data("ais_memorial","app2",GetParam("app2",""));
            $DBConnection->add_data("ais_memorial","nmapp1",GetParam("nmapp1",""));
            $DBConnection->add_data("ais_memorial","nmapp2",GetParam("nmapp2",""));

            $DBConnection->add_data("ais_memorial","rate",$rate);
            $DBConnection->add_data("ais_memorial","nobkt",$TransNo);
            $DBConnection->add_data("ais_memorial","tglbkt",$tglbkt);
            $DBConnection->add_data("ais_memorial","dk",GetParam("dk999",""));
            $DBConnection->add_data("ais_memorial","noorder",GetParam("noorder999",""));
            $DBConnection->add_data("ais_memorial","debtur",GetParam("debtur",""));
            $DBConnection->add_data("ais_memorial","debnam",GetParam("debnam",""));
            $DBConnection->add_data("ais_memorial","skada",GetParam("skada999",""));
            $DBConnection->add_data("ais_memorial","uraian",GetParam("uraian999",""));
            $DBConnection->add_data("ais_memorial","jumlah",StrToNumber(GetParam("total_trans","")));

            //$DBConnection->add_data("ais_memorial","rupiah",StrToNumber(GetParam("total_trans","")) * $rate);
            $DBConnection->add_data("ais_memorial","rupiah",$Rupiah);

            $DBConnection->add_data("ais_memorial","noref",GetParam("noref1",""));
            $DBConnection->add_data("ais_memorial","status",GetParam("status",""));
            $DBConnection->InsertGrid();

            SaveAging ($TransNo);
                        //CheckAndUpdateCache ($TransNo);
            SetLogActivity($TransNo,"Update","");
        }

        //DeleteRecord ("txtCheck",$MaxGrid);
        header("Location: $FileName?PageMode=Form&".PRIMARY_KEY."=".$TransNo);
        exit;
    }
    if (GetParam("FormAction","")=="Delete") {
//      print "Deleted"."<br>\n";
        //DeleteRecord ("txtCheck",$MaxGrid);
                DeleteTrans(GetParam("nobkt",""));
                SetLogActivity($TransNo,"Delete","");
                header("Location: $FileName?");
    }
}

//Field Verification
$Verification  = "False";
$SessionVar    = md5(microtime());
//$SessionVar    = "Stuck";
$VerifyFields = array("source"=>"Source",
                      "nobkt"=>"ID No.",
                      "tglbkt"=>"Transaction Date",
                      "curr"=>"Currency",
                      "skada1"=>"Skada On Line 1",
                      "uraian1"=>"Uraian On Line 1",
                      "kdact999"=>"Unit Usaha On Line 999",
                      "uraian999"=>"Uraian On Line 999",
                      "noref999"=>"Ref On Line 999"
                      );
VerifyHTML ($HTMLFormName,$VerifyFields,$SessionVar,array("debtur"=>"Debtur"));

//modified by dyah. 27 july 2009
$PERIOD_OPENED = 1;
if (GetParam(PRIMARY_KEY,"")) {
    $SQLCount     = "SELECT count(*) FROM `$TableName`";
    $SQLCount    .= "WHERE ".PRIMARY_KEY."='".$PRIMARY_VALUE."' AND urut<>999";
    $Count        = $DBConnection->dbc->get_var($SQLCount);

    $CheckPeriodQuery = "SELECT tglbkt FROM `$TableName`";
    $CheckPeriodQuery .= "WHERE ".PRIMARY_KEY."='".$PRIMARY_VALUE."'";
    $TglTrans = $DBConnection->dbc->get_var($CheckPeriodQuery);
    $PERIOD_OPENED = CheckPeriod($TglTrans);
} else
    $Count=0;

//$NewDataStart = $Count + 1;
$NewDataStart = 1;
$InitFunction = "Masking();Masking2();";

$VerifyGrids = array("uraian"=>"0","jumlah"=>"number","skada"=>"0","dk"=>"","noref"=>"");
$Masking1 = BuildMasking ($HTMLFormName,MutatorFields($FormFields,($MaxGrid+$Count)));
$Masking2 = BuildMasking ($HTMLFormName,array("tglbkt"=>"date","rate"=>"number","duedate"=>"date"),"2");
$JSMasks  = $Masking1.$Masking2;

Init($SessionVar,$JSMasks);
FormGridHTML ($HTMLFormName,$VerifyGrids,$Count,$MaxGrid,$SessionVar);

//************ SET FORM MODE ***************
if (GetParam(PRIMARY_KEY,"")) {
  $FormMode = "Edit";
  if (!isApproved(GetParam("nobkt",""))) $USER_CANPRINT="";
  else $USER_CANPRINT="1";
} else {
  $FormMode = "Add";
  $USER_CANPRINT="";
}

//******* DATABASE GRID QUERY ***********

$SQL = "SELECT ".BuildFields($FormFields)." FROM `$TableName`";
if (GetParam(PRIMARY_KEY,"") && $FormMode="Edit") {
    $SQL      = $SQL."WHERE ".PRIMARY_KEY." ='".GetParam(PRIMARY_KEY,"")."'";
    $SQL     .= " AND ktrasl='".GetOfficeID()."'";
    $SQLGrid  = $SQL." ORDER BY ".PRIMARY_KEY;
}


//******* SHOW FORM CONTENT *********
$TBS = new clsTinyButStrong ;
$TBS->LoadTemplate($MOD_TEMPLATE_DIR."/".$FileTemplate);

//********************************* HTML Header ******************************************
//Select Currency Table
$SQLCurr = "SELECT * FROM tblcurrency";
//Select Department:
$SQLDept = "SELECT * FROM tbldept";
//Select Receipt:
$SQLReceipt = "SELECT * FROM tblreceipt";
//Select Bank:
$SQLBank = "SELECT * FROM tblbank ORDER BY banknam";
//Select Cash:
$SQLCash = "SELECT * FROM tblcash ORDER BY cashnam";
//Select Activity
$SQLAct  = "SELECT  * FROM tblactivity";

$HeaderFields = array("source"=>"",
                      "nobkt"=>"0",
                      "tglbkt"=>"date",
                      "curr"=>"",
                      "rate"=>"number",
                      "debtur"=>"",
                      "debnam"=>"",
                      "kdcash"=>"",
                      "kdbank"=>"",
                      "app1"=>"",
                      "app2"=>"",
                      "nmapp1"=>"",
                      "nmapp2"=>"",
                      "duedate"=>"",
                      "nocek"=>"",
                      "payby"=>""

                      );
//Leave Here Untouch
$FormHName     = BuildHTMLField($HeaderFields,"name",1);
$FormHValue    = BuildHTMLField($HeaderFields,"value",1);
$SQLHeader     = "SELECT ".BuildFields($HeaderFields)." FROM $TableName ";

if (GetParam(PRIMARY_KEY,"") && $FormMode="Edit") $SQLHeader .= "WHERE ".PRIMARY_KEY."='".GetParam(PRIMARY_KEY,"")."' AND ktrasl='".GetOfficeID()."'";
$TBS->MergeBlock("deptblk",$DBMysql,$SQLDept);
$TBS->MergeBlock("bankblk",$DBMysql,$SQLBank);
if (GetParam("ttype","")) $ttype = GetParam("ttype","");
elseif (GetParam("nobkt","")) {
    $SQL = "SELECT IF(kdcash='','Bank','Cash') FROM ais_memorial WHERE ".PRIMARY_KEY."='".GetParam(PRIMARY_KEY,"")."' AND urut=999 ";
    $ttype = $DBConnection->dbc->get_var($SQL);
} else $ttype = "Bank";
$TBS->MergeBlock("cashblk",$DBMysql,$SQLCash);
$TBS->MergeBlock("currformblk",$DBMysql,$SQLCurr);
$TBS->MergeBlock("receiptblk",$DBMysql,$SQLReceipt);
$TBS->MergeBlock("actblk",$DBMysql,$SQLAct);
$TBS->MergeBlock("actblk9",$DBMysql,$SQLAct);

//Total 999 Values
/*
$Fields9       = array("noref999"=>"0","jumlah999"=>"number","skada999"=>"0","untush999"=>"0");
$Form9Value    = BuildHTMLField($Fields9,"value",1);
*/

$SQL999 = "SELECT * FROM ais_memorial "."WHERE ".PRIMARY_KEY." ='".GetParam(PRIMARY_KEY,"")."' AND ktrasl='".GetOfficeID()."' ";
$SQL999 = $SQL999." AND urut = 999";

$TBS->MergeBlock("dept999blk",$DBMysql,$SQLDept);
if (GetParam(PRIMARY_KEY,"")) {$TBS->MergeBlock("blg9",$DBMysql,$SQL999);}
else {
  $TBS->MergeBlock("blg9",$FormCValue);
}

// HTML Value
$TBS->MergeBlock("headbln",$FormHName);
if (GetParam(PRIMARY_KEY,"")) {$TBS->MergeBlock("headblv",$DBMysql,$SQLHeader);}
else {
  $TBS->MergeBlock("headblv",$FormHValue);
}

//********************************* HTML GRID *********************************************
//HTML Grid
$SQLGrid = "SELECT * FROM ais_memorial "."WHERE ".PRIMARY_KEY." ='".GetParam(PRIMARY_KEY,"")."' AND ktrasl='".GetOfficeID()."' AND urut<>999";
//print $SQLGrid;
$Result  = $DBConnection->dbc->get_results($SQLGrid,ARRAY_A);
$Result  = BuildEmptyGrid ($FormFields, $MaxGrid, $Result);

//$Result  = BuildEmptyGrid ($FormFields, $MaxGrid, $Result);

$TBS->MergeBlock("blg",$Result);
$TBS->Show();


//*************** ACTION FUNCTION *******************

function isApproved($nobkt) {
    global $DBConnection;
        $SQL = "SELECT app2 FROM ais_memorial WHERE nobkt='$nobkt'";
        return $DBConnection->dbc->get_var($SQL);
}

function FormInsert() {
    global $DBConnection,$FileName;
    $DBConnection->Insert();
    header("Location: $FileName?PageMode=Form");
    exit;
}

function FormUpdate() {
    global $DBConnection;
    $DBConnection->Update();
}

function FormDelete() {
    global $DBConnection,$FileName,$TableName;
    $DBConnection->deleteGrid("$TableName","nobkt");
    header("Location: $FileName");
    exit;
}

function FormSearch () {

}

function FormCancel () {
    global $FileName;
    header("Location: ".$FileName);
    exit;
}

function FormSaveHutang ($table, $keyField, $FormFields, $SpecialCase="", $PrimaryKey) {
    global $FileName, $MaxGrid, $DBConnection;
    $TotalData = $MaxGrid * 2;
    $NewDataStart = GetParam("NewData","0");
//  $NewDataStart = 1;
    for ($a=1;$a<=$TotalData;$a++) {
            if (substr(GetParam($keyField.$a,""),0,1)=='3' || substr(GetParam($keyField.$a,""),0,1)=='4') {
        foreach ($FormFields as $key=>$formatting) {
            if (!GetParam($keyField.$a,"")) {
                $EndOfData = 1;
                break;
            }
            $value = GetParam($key.$a,"");
            if ($formatting=="number") $value = str_replace(",","",$value);
            if ($formatting=="date") {
                list($day,$month,$year) = explode("/",$value);
                $value = "$year-$month-$day";
            }
            $DBConnection->add_data($table,$key,$value);
        }
        $rate = StrToNumber(GetParam("rate",""));
        $jmh  = StrToNumber(GetParam("jumlah".$a,""));
        $rupiah = $rate * $jmh;
        $DBConnection->add_data($table,"rupiah",$rupiah);
        $DBConnection->add_data($table,"rupiah",$rupiah);
        if ($EndOfData) {
            break;
        }
        if (strlen($SpecialCase)) {
            foreach ($SpecialCase as $key => $value) {
            //  print "add_data($table,$key,$value);<br>";
                $DBConnection->add_data($table,$key,$value);
            }
        }
//      print "$a < $NewDataStart<br>";
        if ($a < $NewDataStart) {
             if (strlen($PrimaryKey)) {
                $DBConnection->UpdateGrid($PrimaryKey,GetParam($PrimaryKey.$a,""));
             } else {
                $DBConnection->UpdateGrid($keyField,GetParam($keyField.$a,""));
             }
        } else {
            $DBConnection->InsertGrid();
        }
          }
    }
    $DBConnection->clearData();
}


function FormSave ($table, $keyField, $FormFields, $SpecialCase="", $PrimaryKey) {
    global $FileName, $MaxGrid, $DBConnection;
    $TotalData = $MaxGrid * 2;
    $NewDataStart = GetParam("NewData","0");
//  $NewDataStart = 1;
    for ($a=1;$a<=$TotalData;$a++) {
        foreach ($FormFields as $key=>$formatting) {
            if (!GetParam($keyField.$a,"")) {
                $EndOfData = 1;
                break;
            }
            $value = GetParam($key.$a,"");
            if ($formatting=="number") $value = str_replace(",","",$value);
            if ($formatting=="date") {
                list($day,$month,$year) = explode("/",$value);
                $value = "$year-$month-$day";
            }
            $DBConnection->add_data($table,$key,$value);
        }
        $rate = StrToNumber(GetParam("rate",""));
        $jmh  = StrToNumber(GetParam("jumlah".$a,""));
        $rupiah = $rate * $jmh;
        $DBConnection->add_data($table,"rupiah",$rupiah);
        if ($EndOfData) {
            break;
        }
        if (strlen($SpecialCase)) {
            foreach ($SpecialCase as $key => $value) {
            //  print "add_data($table,$key,$value);<br>";
                $DBConnection->add_data($table,$key,$value);
            }
        }
//      print "$a < $NewDataStart<br>";
        if ($a < $NewDataStart) {
             if (strlen($PrimaryKey)) {
                $DBConnection->UpdateGrid($PrimaryKey,GetParam($PrimaryKey.$a,""));
             } else {
                $DBConnection->UpdateGrid($keyField,GetParam($keyField.$a,""));
             }
        } else {
            $DBConnection->InsertGrid();
        }
    }
    $DBConnection->clearData();
}
//*************** USER FUNCTION *******************
function buildsqlfield ($table, $arrayfield) {
        global $DBConnection;
        for ($x=0;$x<count($arrayfield);$x++) {
            $DBConnection->add_htmlfield($table,$arrayfield[$x],GetParam($arrayfield[$x],""));
        }
}


function DeleteTrans($nobkt) {
global $DBConnection;
                        $TransNo = $nobkt;
                        $SQL = "SELECT kdcash FROM ais_memorial WHERE nobkt='".$TransNo."' AND urut=999 ";
                        $kdcash = $DBConnection->dbc->get_var($SQL);
                        $SQL = "SELECT kdbank FROM ais_memorial WHERE nobkt='".$TransNo."' AND urut=999 ";
                        $kdbank = $DBConnection->dbc->get_var($SQL);
                        $SQL = "SELECT  jumlah FROM ais_memorial WHERE nobkt='".$TransNo."' AND urut=999 ";
                        $jumlah = $DBConnection->dbc->get_var($SQL);

                        if (ereg("TB",$TransNo)) {
                            $SQL = "UPDATE tblbank SET balance=balance-".$jumlah." WHERE kdbank='".$kdbank."'";
                        } else {
                            $SQL = "UPDATE tblcash SET balance=balance-".$jumlah." WHERE kdcash='".$kdcash."'";
                        }
                        $DBConnection->dbc->query($SQL);
                        $SQL = "DELETE FROM  ais_memorial WHERE nobkt='".GetParam("nobkt","")."'";
                        $DBConnection->dbc->query($SQL);
                        $SQL = "DELETE FROM  ais_piutang WHERE nobkt='".GetParam("nobkt","")."'";
                        $DBConnection->dbc->query($SQL);
                        $SQL = "DELETE FROM  ais_hutang WHERE nobkt='".GetParam("nobkt","")."'";
                        $DBConnection->dbc->query($SQL);

}


function DeleteRecord ($sequence_name,$PageSize) {
    global $DBConnection,$FileName,$TableName;
    for ($x=1;$x<=$PageSize;$x++) {
        if (GetParam($sequence_name.$x,"")) {
            $DBConnection->deleteGrid("$TableName","receipt_id",$sequence_name.$x);
        }
    }
}

function BuildFields ($FormFields) {
        global $DBConnection;
        $str = "";
        foreach ($FormFields as $key=>$formatting) {
            if (strlen($str)) $str .= ",$key";
            else $str = "$key";
        }
        return $str;
}

function MutatorFields ($FormFields, $MaxGrid) {
        global $DBConnection;
        $array_fields = array();
        for ($x=0;$x<$MaxGrid;$x++) {
            foreach ($FormFields as $key=>$formatting){
                $a=$x+1;
                $array_fields[$key.$a] = "$formatting";
            }
        }
        return $array_fields;
}


function FlushCustomer ($TableName, $tmp) {
    global $DBConnection;

    if ($tmp) $TEMPORARY = "TEMPORARY";
    $DBC = new mydb(DB_USER, DB_PASSWORD, DB_NAME2, DB_HOST);

    $SQL = "SELECT kodecustomer,nama,cr_term,kdtype FROM customer ";
    if (GetParam("kdtype","")) $SQL .= " WHERE kdtype='".GetParam("kdtype","")."'";
    $MyResult = $DBC->dbc->get_results($SQL,ARRAY_N);
    if (!$MyResult) return false;
    $session_file = substr(md5(time()), 0, 8);
    $session_file = "/tmp/".$session_file.".tmp";
    BufferToFile ($MyResult,$session_file);

    $DBConnection = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
    $SQL = "DROP TABLE IF EXISTS `$TableName`;";
    $DBConnection->dbc->query($SQL);

    $SQL= "CREATE $TEMPORARY TABLE `$TableName` (
              `kodecustomer` varchar(20) NOT NULL default '',
              `nama` varchar(50) default NULL,
              `cr_term` int(4) default NULL,
              `kdtype` varchar(20) default NULL
              )";
    $DBConnection->dbc->query($SQL);

    $SQL = "LOAD DATA INFILE '$session_file' INTO TABLE $TableName FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'";
    $DBConnection->dbc->query($SQL);
    unlink($session_file);
    return true;
}


function GetLastDateReport($fromSkada,$toSkada,$ktrasl,$curr,$tglbkt) {
    global $DBConnection;
    list($day,$month,$year) = explode("/",$tglbkt);
    $current_date = "$year-$month-$day";

        $SQL = "DELETE FROM ais_piutang_sisa WHERE aging_date>='$current_date' ";
        if ($curr) $SQL .= " AND curr='".$curr."' ";
    if ($fromSkada) $SQL .= " AND fromskada = '".$fromSkada."' ";
        if ($toSkada) $SQL .= " AND toskada = '".$toSkada."' ";
        $DBConnection->dbc->query($SQL);
        //$DBConnection->dbc->debug();

    $SQL = "SELECT max(aging_date) FROM ais_piutang_sisa WHERE aging_date<='$current_date' ";
        if ($curr) $SQL .= " AND curr='".$curr."' ";
    if ($fromSkada) $SQL .= " AND fromskada = '".$fromSkada."' ";
        if ($toSkada) $SQL .= " AND toskada = '".$toSkada."' ";

    return $DBConnection->dbc->get_var($SQL);
}

function CheckAndUpdateCache ($nobkt) {
    global $DBConnection;
//check debit note
              $SQL = "SELECT skada,curr,ktrasl,tglbkt FROM ais_piutang WHERE nobkt='$nobkt' ";
              $MyResult = $DBConnection->dbc->get_results($SQL, ARRAY_A);
        foreach($MyResult as $row)
        {
              GenerateAgingCache($row["skada"],$row["skada"],$row["ktrasl"],$row["curr"],$row["tglbkt"]);
            }
}

function GenerateAgingCache($fromSkada,$toSkada,$ktrasl,$curr,$tglbkt) {
            global $DBConnection;
            //$TEMP = "TEMPORARY";

            //if (!FlushCustomer ("tbldebtur", 1)) return false;
            //$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));

            list($day,$month,$year) = explode("/",$tglbkt);
            $current_date = "$year-$month-$day";
            $LastDate  = GetLastDateReport($fromSkada,$toSkada,$ktrasl,$curr,$tglbkt);

            $SQL = "DROP TABLE IF EXISTS `ais_piutang_terhutang`";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    CREATE $TEMP TABLE `ais_piutang_terhutang` (
                      `ktrasl` varchar(5) default NULL,
                      `tglbkt` date,
                      `nobkt` varchar(16) default NULL,
                                          `nobkta` varchar(16) default NULL,
                      `debtur` varchar(16) default NULL,
                      `debnam` varchar(32) default NULL,
                      `uraian` text default NULL,
                      `curr` varchar(4) default NULL,
                      `jumlah` varchar(32) default NULL,
                      `cr_term` tinyint(4)default NULL,
                                          `top_curr` date,
                                          `top_30` date,
                                          `top_60` date,
                                          `aging` varchar(16)
                    );
                   ";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    INSERT INTO ais_piutang_terhutang
                    SELECT KTRASL, TGLBKT, NOBKT,NOBKTA, tbldebtur.cucode, tbldebtur.cuname, URAIAN, CURR, JUMLAH, IFNULL(CR_TERM,0) AS TOP,

                    ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ) as top_curr,
                    ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ) as top_30,
                    ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ) as top_60,

                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ),'0',
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ),'30',
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ),'60','60g')))

                    FROM ais_piutang
                    LEFT JOIN tbldebtur ON ais_piutang.DEBTUR=tbldebtur.cucode
                    WHERE srt='1' AND ais_piutang.tglbkt<='$current_date'
                   ";

            if (strlen($LastDate))
            $SQL .= "AND ais_piutang.tglbkt>'$LastDate' ";

            if ($fromSkada)
            $SQL .= "AND ais_piutang.skada >= '".$fromSkada."' ";

            if ($curr)
            $SQL .= "AND ais_piutang.curr = '".$curr."' ";

            if ($toSkada)
            $SQL .= "AND ais_piutang.skada <= '".$toSkada."' ";

                        //print $SQL;die();
            if ($ktrasl)  $SQL .= "AND ais_piutang.ktrasl = '".$ktrasl."' ";

            $SQL .= "order by nobkt ";
            $DBConnection->dbc->query($SQL);

            $SQL = "DROP TABLE IF EXISTS `ais_piutang_terbayar`";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    CREATE $TEMP TABLE `ais_piutang_terbayar` (
                      `nobkt` varchar(16) default NULL,
                      `nobkta` varchar(16) default NULL,
                                          `bayar` decimal(15,3) default NULL,
                      `debtur` varchar(64) default NULL,
                      `debnam` varchar(128) default NULL
                    ) ;
                   ";
            $DBConnection->dbc->query($SQL);

            $SQL = "
                    INSERT INTO ais_piutang_terbayar
                    SELECT NOBKT, NOBKTA, SUM(JUMLAH),debtur,debnam
                    FROM ais_piutang
                                        LEFT JOIN tbldebtur ON ais_piutang.DEBTUR=tbldebtur.cucode
                    WHERE srt='2' AND ais_piutang.tglbkt<='$current_date' ";

            if (strlen($LastDate))
            $SQL .= "AND ais_piutang.tglbkt>'$LastDate' ";

                        //print $SQL;die();
            if (GetOfficeID())
                        $SQL .= "AND ais_piutang.ktrasl = '".GetOfficeID()."' ";

            if ($curr)
            $SQL .= "AND ais_piutang.curr = '".$curr."' ";

            $SQL .= "group by nobkta order by nobkta";
                        //print $SQL;die();
            $DBConnection->dbc->query($SQL);
            //$DBConnection->dbc->debug();

//Auto Update Using Cache Begin Here
             if (strlen($LastDate)) {

                $SQL = "DROP TABLE IF EXISTS `ais_piutang_sisa_period`";
                $DBConnection->dbc->query($SQL);

                $SQL = "
                        CREATE $TEMP TABLE `ais_piutang_sisa_period` (
                          `ktrasl` varchar(5) default NULL,
                          `tglbkt` date,
                          `aging_date` date,
                          `nobkt` varchar(16) default NULL,
                          `debtur` varchar(16) default NULL,
                          `debnam` varchar(32) default NULL,
                          `uraian` text default NULL,
                                                  `cr_term` tinyint(4)default NULL,
                          `jumlah` decimal(15,2) default NULL,
                                                  `aging` varchar(16),
                                                  `fromskada` varchar(16),
                                                  `toskada` varchar(16),
                                                  `curr`varchar(5)
                        )
                       ";
                $DBConnection->dbc->query($SQL);
                 $SQL = "
                          INSERT INTO ais_piutang_sisa_period
                          SELECT *
                          FROM ais_piutang_sisa
                          WHERE aging_date='$LastDate' AND fromskada='".$fromSkada."' AND toskada='".$toSkada."'
                        ";
                              if ($curr)  $SQL .= " AND curr='".$curr."' ";

                 $DBConnection->dbc->query($SQL);

                       $SQL = "
                          INSERT INTO ais_piutang_sisa_period
                                  SELECT ktrasl,tglbkt,'$current_date',nobkt,debtur,debnam,uraian,cr_term,jumlah,aging,'".$fromSkada."','".$toSkada."','".$curr."'
                                  FROM ais_piutang_terhutang
                        ";
                 $DBConnection->dbc->query($SQL);


                     $SQL = "
                              INSERT INTO ais_piutang_sisa
                              SELECT
                              ais_piutang_sisa_period.ktrasl,
                              ais_piutang_sisa_period.tglbkt,
                              '$current_date',
                              ais_piutang_sisa_period.nobkt,
                              ais_piutang_sisa_period.debtur,
                              ais_piutang_sisa_period.debnam,
                              ais_piutang_sisa_period.uraian,
                              ifnull(ais_piutang_sisa_period.cr_term, 0) AS top,
                              ifnull(ais_piutang_sisa_period.jumlah,0)-ifnull(ais_piutang_terbayar.bayar,0),

                              IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_piutang_sisa_period.cr_term DAY ),'0',
                              IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_piutang_sisa_period.cr_term DAY ),'30',
                              IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_piutang_sisa_period.cr_term DAY ),'60','60g'))),
                                          '".$fromSkada."','".$toSkada."','".$curr."'

                              FROM
                              ais_piutang_sisa_period
                              LEFT JOIN ais_piutang_terbayar ON (ais_piutang_sisa_period.nobkt = ais_piutang_terbayar.nobkta)
                              WHERE (ifnull(ais_piutang_terbayar.nobkt, '') = '') OR ifnull(ais_piutang_sisa_period.jumlah-ais_piutang_terbayar.bayar,0) <> 0
                              ORDER BY debtur,nobkt
                            ";
                     $DBConnection->dbc->query($SQL);

             } else {
                     $SQL = "
                              INSERT INTO ais_piutang_sisa
                              SELECT
                              ais_piutang_terhutang.ktrasl,
                              ais_piutang_terhutang.tglbkt,
                              '$current_date',
                              ais_piutang_terhutang.nobkt,
                              ais_piutang_terhutang.debtur,
                              ais_piutang_terhutang.debnam,
                              ais_piutang_terhutang.uraian,
                              ifnull(ais_piutang_terhutang.cr_term, 0) AS top,
                              ifnull(ais_piutang_terhutang.jumlah,0)-ifnull(ais_piutang_terbayar.bayar,0),

                            IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_piutang_terhutang.cr_term DAY ),'0',
                            IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_piutang_terhutang.cr_term DAY ),'30',
                            IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_piutang_terhutang.cr_term DAY ),'60','60g'))),
                                          '".$fromSkada."','".$toSkada."','".$curr."'

                              FROM
                              ais_piutang_terhutang
                              LEFT JOIN ais_piutang_terbayar ON (ais_piutang_terhutang.nobkt = ais_piutang_terbayar.nobkta)
                              WHERE (ifnull(ais_piutang_terbayar.nobkt, '') = '') OR ifnull(ais_piutang_terhutang.jumlah-ais_piutang_terbayar.bayar,0) <> 0
                              ORDER BY debtur,nobkt
                            ";
                    $DBConnection->dbc->query($SQL);

            }
//Auto Update Using Cache End Here

//          $SQL = "SELECT * FROM ais_aging WHERE aging_date='$current_date'";

}
?>
